package com.example.myapplication.database;

import android.annotation.SuppressLint;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

import com.example.myapplication.model.Note;
import com.example.myapplication.utils.TimeUtil;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class DatabaseHelper extends SQLiteOpenHelper {

    // 数据库名和版本
    private static final String DATABASE_NAME = "notes.db";
    private static final int DATABASE_VERSION = 1;

    // 表名常量
    private static final String TABLE_NOTES = "Notes";
    private static final String TABLE_CATEGORIES = "Categories";
    private static final String TABLE_NOTE_CATEGORIES = "NoteCategories";

    // 表结构常量
    // Notes 表结构
    private static final String CREATE_TABLE_NOTES = "CREATE TABLE " + TABLE_NOTES + " (" +
            "id INTEGER PRIMARY KEY AUTOINCREMENT," +
            "title TEXT NOT NULL," +
            "content TEXT," +
            "created_at DATETIME DEFAULT CURRENT_TIMESTAMP," +
            "updated_at DATETIME DEFAULT CURRENT_TIMESTAMP" +
            ");";


    // Categories 表结构
    private static final String CREATE_TABLE_CATEGORIES = "CREATE TABLE " + TABLE_CATEGORIES + " (" +
            "id INTEGER PRIMARY KEY AUTOINCREMENT," +
            "name TEXT NOT NULL UNIQUE," +
            "created_at DATETIME DEFAULT CURRENT_TIMESTAMP" +
            ");";

    // NoteCategories 表结构
    private static final String CREATE_TABLE_NOTE_CATEGORIES = "CREATE TABLE " + TABLE_NOTE_CATEGORIES + " (" +
            "note_id INTEGER NOT NULL," +
            "category_id INTEGER NOT NULL," +
            "created_at DATETIME DEFAULT CURRENT_TIMESTAMP," +
            "PRIMARY KEY (note_id, category_id)," +
            "FOREIGN KEY (note_id) REFERENCES " + TABLE_NOTES + "(id) ON DELETE CASCADE," +
            "FOREIGN KEY (category_id) REFERENCES " + TABLE_CATEGORIES + "(id) ON DELETE CASCADE" +
            ");";

    public DatabaseHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL(CREATE_TABLE_NOTES);
        db.execSQL(CREATE_TABLE_CATEGORIES);
        db.execSQL(CREATE_TABLE_NOTE_CATEGORIES);

        // 插入默认分类
        ContentValues defaultCategory = new ContentValues();
        defaultCategory.put("name", "未命名");
        db.insert(TABLE_CATEGORIES, null, defaultCategory);
    }


    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_NOTE_CATEGORIES);
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_CATEGORIES);
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_NOTES);
        onCreate(db);
    }

    // 添加笔记（自动分配分类）
    public void addNote(String title, String content, List<Integer> tagIds, Integer categoryId) {
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues values = new ContentValues();
        values.put("title", title);
        values.put("content", content);
        values.put("created_at", TimeUtil.getTime()); // 使用当前时间戳

        // 插入笔记并获取笔记ID
        long noteId = db.insert(TABLE_NOTES, null, values);

        if (noteId != -1) {
            // 如果没有传入分类ID，自动分配默认分类“未命名”
            if (categoryId == null) {
                categoryId = getCategoryIdByName("未命名"); // 获取“未命名”分类的ID
            }

            // 将笔记与分类关联
            if (categoryId != -1) {
                ContentValues categoryValues = new ContentValues();
                categoryValues.put("note_id", noteId);
                categoryValues.put("category_id", categoryId);
                db.insert(TABLE_NOTE_CATEGORIES, null, categoryValues);
            }
        }

        db.close();
    }

    // 获取分类ID（根据名称）
    @SuppressLint("Range")
    public int getCategoryIdByName(String name) {
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.query(TABLE_CATEGORIES, new String[]{"id"}, "name=?", new String[]{name}, null, null, null);
        if (cursor.moveToFirst()) {
            return cursor.getInt(cursor.getColumnIndex("id"));
        }
        cursor.close();
        return -1; // 未找到对应的分类
    }


    // 按分类查询笔记
    @SuppressLint("Range")
    public List<Note> getNotesByCategory(String categoryName) {
        List<Note> notes = new ArrayList<>();
        SQLiteDatabase db = this.getReadableDatabase();

        // 获取分类ID
        int categoryId = getCategoryIdByName(categoryName);

        // 如果分类ID有效，则继续查询
        if (categoryId != -1) {
            String query = "SELECT * FROM " + TABLE_NOTES + " n INNER JOIN " + TABLE_NOTE_CATEGORIES +
                    " nc ON n.id = nc.note_id WHERE nc.category_id=?";
            Cursor cursor = db.rawQuery(query, new String[]{String.valueOf(categoryId)});
            while (cursor.moveToNext()) {
                notes.add(new Note(
                        cursor.getInt(cursor.getColumnIndex("id")),
                        cursor.getString(cursor.getColumnIndex("title")),
                        cursor.getString(cursor.getColumnIndex("content")),
                        cursor.getString(cursor.getColumnIndex("created_at")),
                        cursor.getString(cursor.getColumnIndex("updated_at"))
                ));
            }
            cursor.close();
        }

        db.close();
        return notes;
    }


    // 按内容模糊查询笔记
    @SuppressLint("Range")
    public List<Note> search(String query) {
        List<Note> notes = new ArrayList<>();
        SQLiteDatabase db = this.getReadableDatabase();
        String sql = "SELECT * FROM " + TABLE_NOTES + " WHERE title LIKE ? OR content LIKE ?";
        Cursor cursor = db.rawQuery(sql, new String[]{"%" + query + "%", "%" + query + "%"});
        while (cursor.moveToNext()) {
            notes.add(new Note(
                    cursor.getInt(cursor.getColumnIndex("id")),
                    cursor.getString(cursor.getColumnIndex("title")),
                    cursor.getString(cursor.getColumnIndex("content")),
                    cursor.getString(cursor.getColumnIndex("created_at")),
                    cursor.getString(cursor.getColumnIndex("updated_at"))
            ));
        }
        cursor.close();
        db.close();
        return notes;
    }

    // 查询所有笔记
    public List<Note> findAll() {
        SQLiteDatabase db = getReadableDatabase(); // 使用只读数据库更合适
        String sql = "SELECT * FROM Notes";

        // 执行查询，rawQuery 返回 Cursor 对象
        Cursor cursor = db.rawQuery(sql, null);
        List<Note> list = new ArrayList<>();

        // 遍历查询结果
        while (cursor.moveToNext()) {
            Note note = new Note();

            // 获取列的值
            @SuppressLint("Range")
            int id = cursor.getInt(cursor.getColumnIndex("id"));
            @SuppressLint("Range")
            String title = cursor.getString(cursor.getColumnIndex("title"));
            @SuppressLint("Range")
            String content = cursor.getString(cursor.getColumnIndex("content"));

            @SuppressLint("Range")
            String time = cursor.getString(cursor.getColumnIndex("created_at"));

            // 设置 Note 对象的属性
            note.setId(id);
            note.setTitle(title);
            note.setContent(content);
            note.setCreatedAt(time);

            // 将 Note 对象添加到列表中
            list.add(note);
        }
        Log.d("DatabaseHelper", "findAll: " + list);
        // 关闭 Cursor 和数据库
        cursor.close();
        db.close();

        return list;
    }
    //删除笔记
    public boolean deleteNoteById(int id) {
        SQLiteDatabase db = this.getWritableDatabase();
        int rows = db.delete("Notes", "id = ?", new String[]{String.valueOf(id)});
        return rows > 0; // 如果删除的行数大于0，表示删除成功
    }
    //更新笔记
    public void updateNote(Note note) {
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues values = new ContentValues();
        values.put("title", note.getTitle());
        values.put("content", note.getContent());
        values.put("updated_at", TimeUtil.getTime());
        db.update("Notes", values, "id = ?", new String[]{String.valueOf(note.getId())});
        db.close();
    }

    //获取所有分类
    public List<String> getAllCategories() {
        List<String> categories = new ArrayList<>();
        SQLiteDatabase db = this.getReadableDatabase();
        String sql = "SELECT name FROM Categories";
        Cursor cursor = db.rawQuery(sql, null);
        while (cursor.moveToNext()) {
            categories.add(cursor.getString(0));
        }
        cursor.close();
        db.close();
        Log.d("DatabaseHelper", "getAllCategories: " + categories);
        return categories;
    }
    //新增分类
    public void addCategory(String categoryName) {
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues values = new ContentValues();
        values.put("name", categoryName); // Assuming the category table has a 'name' column
        db.insert("Categories", null, values);
        db.close();
    }

    // 更新笔记分类的方法
    public void updateNoteCategory(int noteId, int newCategoryId) {
        SQLiteDatabase db = this.getWritableDatabase();

        // 删除旧的分类绑定
        db.delete(TABLE_NOTE_CATEGORIES, "note_id = ?", new String[]{String.valueOf(noteId)});

        // 插入新的分类绑定
        ContentValues values = new ContentValues();
        values.put("note_id", noteId);
        values.put("category_id", newCategoryId);
        db.insert(TABLE_NOTE_CATEGORIES, null, values);

        db.close();
    }

}

